How to Sum Values While Skipping Columns in Excel: Simple Methods Explained

When working with large datasets in Excel, you might often need to perform calculations across specific columns while skipping others. One common requirement is summing values from either odd or even-numbered columns. Rather than manually selecting each column, Excel offers several powerful methods to automate this task. Whether you prefer using built-in functions like SUMPRODUCT, leveraging array formulas, or manually selecting columns, these techniques can streamline your workflow and save you time. In this guide, we’ll walk you through easy-to-follow methods for summing values while skipping columns, tailored to your needs for odd or even column selections.

To sum values in Excel while skipping specific columns (e.g., even or odd columns), you can use different methods depending on your needs. Here are some simple approaches:

1. Summing Odd or Even Columns Using SUM Function

Sum Odd Columns:
If you want to sum values in the odd-numbered columns (e.g., Column 1, Column 3, Column 5...), you can use the SUM function with the INDEX function combined with COLUMN to select specific columns.

Example (Sum Odd Columns A, C, E for rows 1 to 10):

=SUMPRODUCT((MOD(COLUMN(A1:E10), 2) = 1) * (A1:E10))

Sum Even Columns:
To sum values in the even-numbered columns (e.g., Column 2, Column 4, Column 6...), you can adjust the formula to target even columns.

Example (Sum Even Columns B, D, F for rows 1 to 10):

=SUM(IF(MOD(COLUMN(A1:Z1),2)=1,A1:Z1,0))

2. Using Array Formulas (CTRL + SHIFT + ENTER) 

Sum Odd Columns:
You can use an array formula that sums values in odd-numbered columns. Array formulas are typically entered by pressing CTRL + SHIFT + ENTER.

Example:

=SUM(IF(MOD(COLUMN(A1:Z1),2)=1,A1:Z1,0))

After typing the formula, press CTRL + SHIFT + ENTER.

Sum Even Columns:
Similarly, to sum values in even columns, modify the condition for the even columns.

Example:

=SUM(IF(MOD(COLUMN(A1:Z1),2)=0,A1:Z1,0))

After typing the formula, press CTRL + SHIFT + ENTER.

3. Sum by Manually Selecting Columns 

If you don’t want to use formulas, you can manually select the columns you want to sum. For instance:

• Click on the first cell in an odd-numbered column, hold Ctrl, and select additional odd columns (e.g., A, C, E, etc.).
• Then, use the SUM formula: =SUM(A1, C1, E1, ...).

This approach may be simpler if you’re working with a small dataset but can get tedious with large datasets.

Conclusion
For flexibility and ease, the SUMPRODUCT method works well for summing either odd or even columns without requiring manual column selection. The array formula is another option but requires pressing CTRL + SHIFT + ENTER.